package dao;

import model.BorrowRecord;
import model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.Objects;
import java.util.Random;

public class UserDao {

    public static int deleteByUsername(Connection connection, String username) throws Exception {
        int ret = 0;
        String sql = "DELETE FROM users WHERE username = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.executeUpdate();
        return  ret;
    }

    public static ResultSet getAll(Connection connection) throws Exception{
        String sql = "SELECT *FROM users";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        return preparedStatement.executeQuery();
    }

    public static int updateInformation(Connection connection, User user) throws Exception {
        int ret = 0; // 正确返回
        if (Objects.equals(user.getPassword(), "")) {
            ret = 1;
        }else {
            String sql = "UPDATE users SET password = ?, email = ? WHERE username = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getPassword());
            preparedStatement.setString(2, user.getEmail());
            preparedStatement.setString(3, user.getUsername());
            preparedStatement.executeUpdate();
        }
        return  ret;
    }


    public static User login(Connection connection, User user) throws Exception {
        User retUser = null;
        String select = "Select * From users WHERE username = ? and password = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(select);
        preparedStatement.setString(1, user.getUsername());
        preparedStatement.setString(2, user.getPassword());
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            retUser = new User();
            retUser.setUsername(resultSet.getString("username"));
            retUser.setPassword(resultSet.getString("password"));
            retUser.setEmail(resultSet.getString("email"));
        }
        return retUser;
    }

    public static int register(Connection connection, User user) throws Exception {
        int status = 0;
        if (Objects.equals(user.getUsername(), "")) {
            status = 2;// 表示用户名为空
        } else if (Objects.equals(user.getPassword(), "")) {
            status = 3; // 表示密码为空
        } else {
            String insert = "INSERT INTO users VALUES (?, ?, ?)";
            String select = "SELECT * FROM users WHERE username = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(select);
            preparedStatement.setString(1, user.getUsername());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                status = 1; // 返回状态表示用户名已存在
            } else {
                preparedStatement = connection.prepareStatement(insert);
                preparedStatement.setString(1, user.getUsername());
                preparedStatement.setString(2, user.getPassword());
                preparedStatement.setString(3, user.getEmail());
                preparedStatement.executeUpdate();
            }
        }
        return status;
    }

    public static String returnBook (Connection connection, User user, int bookID, String positon) throws Exception {
        String statics = "还书成功！";
        String sql = "SELECT * FROM borrowrecord WHERE bookid = ? AND username = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, bookID);
        preparedStatement.setString(2, user.getUsername());
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            statics = "未找到借阅记录";
        } else {
            String delete = "UPDATE borrowrecord SET return_date = ? WHERE id = ?";
            preparedStatement = connection.prepareStatement(delete);
            preparedStatement.setTimestamp(1, new Timestamp(System.currentTimeMillis()));

            preparedStatement.setInt(2, resultSet.getInt("id"));
            preparedStatement.executeUpdate();
            String update = "UPDATE books SET status = 1, position = ? WHERE bookid = ?";
            preparedStatement = connection.prepareStatement(update);
            preparedStatement.setInt(2, resultSet.getInt("bookid"));
            preparedStatement.setString(1, positon);
            preparedStatement.executeUpdate();
            statics = "还书成功";
        }
        return statics;
    }

    public static String borrowRecord (Connection connection, User user, int bookID) throws Exception{
        String status = "登记成功";
        String sql = "SELECT books.status FROM books WHERE bookid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, bookID);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            status = "查无此书";
        } else if (resultSet.getInt("status") == 0) {
            status = "此书已被借出";
        } else if (resultSet.getInt("status") == 1) {
            BorrowRecord borrowRecord = new BorrowRecord();
            String getMaxSql = "SELECT max(borrowrecord.id) as 'MAX' FROM borrowrecord";
            preparedStatement = connection.prepareStatement(getMaxSql);
            ResultSet resultSet1 = preparedStatement.executeQuery();
            resultSet1.next();
            borrowRecord.setId(resultSet1.getInt("MAX") + 1);
            borrowRecord.setBorrow_date(new Timestamp(System.currentTimeMillis()));
            borrowRecord.setUsername(user.getUsername());
            borrowRecord.setBookId(bookID);
            String insert = "INSERT INTO borrowrecord VALUES (?,?,?,?,null)";
            preparedStatement = connection.prepareStatement(insert);
            preparedStatement.setInt(1,borrowRecord.getId());
            preparedStatement.setInt(2,borrowRecord.getBookId());
            preparedStatement.setString(3,borrowRecord.getUsername());
            preparedStatement.setTimestamp(4,borrowRecord.getBorrow_date());
            preparedStatement.executeUpdate();
            String update = "UPDATE books SET status = 0, position = null WHERE bookid = ?";
            preparedStatement = connection.prepareStatement(update);
            preparedStatement.setInt(1, bookID);
            preparedStatement.executeUpdate();
        }else {
            status = "状态错误请联系管理员";
        }
        return status;
    }

}
